from pymysql import Connection
from pathlib import Path

def connectDb():
    '''
    用于连接数据库
    :return: 数据库链接
    '''
    try:
        conn = Connection(
            host='localhost',
            port=3306,
            user='root',
            password='123456',
            database='db03',
            charset='utf8',
            autocommit=True
        )
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        return None

def insertData(conn, data):
    '''
    用于将数据插入数据库
    :param conn: 数据库连接对象
    :param data: 要插入的数据
    :return: None
    '''
    if not conn:
        print("数据库连接未建立，无法插入数据")
        return

    # 检查数据格式
    if len(data) < 3:
        print(f"数据格式错误，需要至少3个字段，当前数据: {data}")
        return

    cursor = None
    try:
        cursor = conn.cursor()
        sql = '''
            insert into log_rec values (null, %s, %s, %s)
        '''

        cursor.execute(sql, (data[0], data[1], data[2]))
        print(f'添加成功: {data}')
    except Exception as e:
        print(f"插入数据失败: {e}, 数据: {data}")
    finally:
        if cursor:
            cursor.close()  # 确保游标关闭

if __name__ == '__main__':
    file_path = "server_logs.txt"
    conn = connectDb()  # 建立一次连接，重复使用

    if conn:
        try:
            with open(file_path, 'r', encoding='utf-8') as file:
                for line_num, line in enumerate(file, 1):
                    cleaned_line = line.strip()
                    if not cleaned_line:  # 跳过空行
                        continue
                    parts = cleaned_line.split(',')
                    insertData(conn, parts)  # 传入连接对象
            print('数据导入完成')
        except FileNotFoundError:
            print(f"错误: 文件 '{file_path}' 不存在")
        except Exception as e:
            print(f"读取文件时发生错误: {e}")
        finally:
            if conn:
                conn.close()  # 所有操作完成后关闭连接
    else:
        print("无法建立数据库连接，程序退出")
